OpenEdge Development:<br />Progress 4GL Handbook Determining the current number of rows in a query

You can use the NUM-RESULTS function to determine how many rows there are in the current results list:

NUM-RESULTS ( `query-name` )

This INTEGER function returns the number of rows currently in the query’s results list. As with the QUERY-OFF-END function, the query-name is an expression, which can be either the quoted name of the query or a variable containing the name.

The phrase “currently in the query’s results list” requires some explanation. The results list is a list of the row identifiers of all rows that satisfy the query, and that have already been retrieved.

Progress normally builds up the results list as you walk through the query using the GET statement. Therefore, when you first open a query with a FOR EACH clause in the OPEN QUERY statement, the results list is empty and the NUM-RESULTS function returns zero.

As you move through the query using the GET NEXT statement, Progress adds each new row’s identifier to the results list and increments the value returned by NUM-RESULTS. For example, this example retrieves all the Customers in the state of Louisiana using a query. For each row, it displays the Customer Number, Name, and the value of NUM-RESULTS:

DEFINE QUERY CustQuery FOR Customer. OPEN QUERY CustQuery FOR EACH Customer WHERE State = "LA". GET FIRST CustQuery. DO WHILE NOT QUERY-OFF-END("CustQuery"): DISPLAY Customer.CustNum Customer.NAME `NUM-RESULTS("CustQuery")` LABEL "Rows" WITH FRAME CustFrame `15 DOWN`. GET NEXT CustQuery. `DOWN WITH FRAME CustFrame`. END.

DEFINE QUERY CustQuery FOR Customer. 
     
OPEN QUERY CustQuery FOR EACH Customer WHERE State = "LA". 
GET FIRST CustQuery. 
DO WHILE NOT QUERY-OFF-END("CustQuery"): 
    DISPLAY Customer.CustNum Customer.NAME  
            NUM-RESULTS("CustQuery") LABEL "Rows" 
        WITH FRAME CustFrame 15 DOWN. 
    GET NEXT CustQuery. 
    DOWN WITH FRAME CustFrame. 
END.

When you run the procedure, you see the value of NUM-RESULTS change as each new row is retrieved, as shown in Figure 10–3.

Using a DOWN frame and the DOWN WITH statement

As a small digression, it is necessary to explain a couple of statements in this example that you haven’t seen before. They illustrate one of the key characteristics about queries: there’s no built-in block scoping or iteration in a query. First, here’s the new phrase on the DISPLAY statement:

WITH FRAME CustFrame `15 DOWN`.

You learned a little about down frames in Chapter 2, "Using Basic 4GL Constructs," and Chapter 5, " Examining the Code the AppBuilder Generates." A down frame is a frame that can display more than one row of data, each showing the same fields for a different record in a report-like format. In the examples you wrote in earlier chapters, you didn’t have to specify the DOWN phrase to indicate how many rows the frame should have. Progress gave you a down frame with a default number of rows automatically.

Why doesn’t it do that in this case? Because a query is not associated with a particular block, and doesn’t have any automatic iteration, Progress doesn’t know how the data is going to be displayed. So by default, it just gives you a one down frame that displays a single record.

`DOWN WITH FRAME CustFrame.`

No, this is not a political protest slogan! Rather, it tells Progress to display a row in the frame CustFrame, and then to position down a row in the frame before displaying the next row. If you don’t use this statement, then even if you define the frame to be 15 DOWN, all the rows are displayed on top of each other on the first row of the frame. Once again, this is because Progress doesn’t know how you’re going to display the data. It does not associate iteration through a result set with your DO block automatically, as it would with a FOR EACH block. Therefore, you have to tell it what to do.

Retrieving query results in advance

The value of NUM-RESULTS does not always increment as you execute GET NEXT statements and operate on each row, however. There are various factors that force Progress to retrieve all the results in advance of presenting you with any data.

One of these is under your direct control: the PRESELECT option on the OPEN QUERY statement. When you use a PRESELECT EACH rather than a FOR EACH statement to define the data selection, you are telling Progress to retrieve all the records that satisfy the query in advance and to save off their record identifiers in temporary storage. Then Progress again retrieves the records using their identifiers as you need them. As discussed in "OPEN and CLOSE QUERY statements" section, you typically use the PRESELECT option to make sure that the set of records is not disturbed by changes that you make as you work your way through the list, such as changing a key value in such a way as to change a record’s position in the list.

Change the OPEN QUERY statement in the sample procedure:


OPEN QUERY CustQuery `PRESELECT` EACH Customer WHERE State = "LA".

Run the procedure again to see the different value of NUM-RESULTS:

All the records are pre-retrieved. Therefore, the value of NUM-RESULTS is the same no matter what record you are positioned to. This means that you could use the PRESELECT option to display, or otherwise make use of, the total number of records in the results list before displaying or processing all the data.

Another factor that can force Progress to pre-retrieve all the data is a sort that cannot be satisfied using an index.

Change the OPEN QUERY statement back to use a FOR EACH block and then try sorting the data in the query by the Customer Name:


OPEN QUERY CustQuery FOR EACH Customer WHERE State = "LA" `BY Name`.

Run the query:

The Name field is indexed, so Progress can satisfy the BY phrase and present the data in the sort order you want by using the index to traverse the database and retrieve the records.

By contrast, try sorting on the City field:


OPEN QUERY CustQuery FOR EACH Customer WHERE State = "LA" `BY City`.

Add the City field to the DISPLAY list and rerun the procedure to see the result:

There is no index on the City field, so Progress has to retrieve all 13 of the records for Customers in Louisiana in advance to sort them by the City field before presenting them to your procedure. Therefore, NUM-RESULTS is equal to the total number of records from the beginning, as soon as the query is opened.